package com.xml2db;

import java.util.List;

import org.springframework.stereotype.Component;

/**
 * 
 * sql语句生成
 * 
 * @author levy
 */
@Component
public class SqlGenerator {

	public String insertSQL(DBTable table) {
		return this.insertSQL(table, null);
	}

	/**
	 * 生成插入语句
	 * 
	 * @param table
	 *            表名
	 * @param fields
	 *            待插入字段
	 * @param values
	 *            待插入值
	 * @param condition
	 *            条件
	 * @return
	 * @since 2016年9月23日 下午2:31:56
	 * @author levy
	 */
	public String insertSQL(DBTable table, String condition) {
		List<String> fields = table.getFields();
		StringBuilder fieldsb = new StringBuilder();
		StringBuilder symbol = new StringBuilder();
		for (int i = 0; i < fields.size(); i++) {
			if (i == 0) {
				fieldsb.append(fields.get(0));
				symbol.append("?");
			} else {
				fieldsb.append(",").append(fields.get(i));
				symbol.append(",").append("?");
			}
		}

		StringBuilder sb = new StringBuilder();
		sb.append("insert into ").append(table.getTableName()).append("("
				+ fieldsb.toString() + ") value(" + symbol.toString() + ")  ");
		if (condition != null && condition.trim().length() > 0) {
			sb.append(condition);
		}
		return sb.toString();
	}

	public String createTableSQL(DBTable table) {
		return this.createTableSQL(table, null);
	}

	/***
	 * 生成创建表sql语句
	 * 
	 * @param table
	 * @param condition
	 * @return
	 */
	public String createTableSQL(DBTable table, String condition) {

		List<String> fields = table.getFields();
		List<String> fieldTypes = table.getFieldsTypes();

		StringBuilder field2type = new StringBuilder();
		field2type.append("(");
		for (int i = 0; i < fields.size(); i++) {
			String type = fieldTypes.get(i);
			type = "string".equalsIgnoreCase(type) ? "varchar(150)" : type;// excel文件配的类型只有string和int
			field2type.append(fields.get(i)).append(" " + type);
			if (i != fields.size() - 1)
				field2type.append(",");
		}
		field2type.append(");");
		StringBuilder sql = new StringBuilder();
		sql.append(" create table ").append(table.getTableName())
				.append(field2type.toString());
		if (condition != null && condition.trim().length() > 0) {
			sql.append(condition);
		}
		return sql.toString();
	}

	/**
	 * 删表语句
	 * 
	 * @param table
	 * @return
	 */
	public String dropTableSQL(DBTable table) {
		return "drop table if exists " + table.getTableName();
	}
}
